# -*- codeing = utf-8 -*-
# @Time : 2022/11/29 15:32
# @Author : 小马
# @File: GetData.py
# @Software : PyCharm

#从深圳市水务局获取到深圳各个区的污水处理数据
import urllib.request,urllib.error
import pymysql
import re
from bs4 import BeautifulSoup
import xlwt

findaddress=re.compile(r'<a href="(.*)" target="_blank">20(.*)</a>')
finddataset=re.compile(r'<p style="text-align: center;"><strong>(.*?)</strong></p>')
finddataset2020=re.compile(r'<p style="text-align: left;"><strong>(.*?)</strong></p>')
baseurl="http://swj.sz.gov.cn/xxgk/zfxxgkml/szswgk/tjsj/psgltj/"
#元特区 福田区 罗湖区 盐田区 南山区 宝安区 大鹏新区 龙宝区 龙华区 坪山区 光明区
def ask(url):
    headers = {
        "User-Agent": "Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/94.0.4606.71 Safari/537.36 Core/1.94.178.400 QQBrowser/11.2.5170.400"}
    request = urllib.request.Request(url=url, headers=headers)
    html = ""
    try:
        response = urllib.request.urlopen(request)
        html = response.read().decode("utf-8")
    except urllib.error.URLError as e:
        if hasattr(e, "code"):  # hasattr() 函数用来判断某个类实例对象是否包含指定名称的属性或方法。
            print(e.code)
        if hasattr(e, "reason"):
            print(e.reason)
    return html

def getaddress(baseurl):
    addressList=[]
    htmll=ask(baseurl)
    soup=BeautifulSoup(htmll,"html.parser")
    for item in soup("a",target="_blank"):
        item=str(item)
        add=re.findall(findaddress,item)
        if(len(add)>0):
            addressList.append(add[0][0])
    return addressList
def get2021():
    url="http://swj.sz.gov.cn/xxgk/zfxxgkml/szswgk/tjsj/psgltj/content/post_9685824.html"
    html=ask(url)
    soup=BeautifulSoup(html,"html.parser")
    year="2021"
    districtList=[]

    districtname=[]
    sum1=[]
    sum2=[]
    for i in soup.tbody:
        i=str(i)
        data=re.findall(finddataset,i)
        if(len(data)>0):
            if(len(data)==1):
                districtname.append(data[0])
            if(len(data)==3):
                sum1.append(data[1])
                sum2.append(data[2])
    # for item in soup("p",style="text-align: center"):
    #     print(item)
    # for i in districtname:
    #     print(i)
    # for i in sum1:
    #     print(i)
    # for i in sum2:
    #     print(i)
    for i in range(0,7):
        district = []
        district.append(year)
        district.append(districtname[i])
        district.append(sum1[i])
        district.append(sum2[i])
        districtList.append(district)
    return districtList
def get2020():
    url="http://swj.sz.gov.cn/xxgk/zfxxgkml/szswgk/tjsj/psgltj/content/post_8666858.html"
    html = ask(url)
    soup = BeautifulSoup(html, "html.parser")
    year = "2020"
    districtList = []

    districtname = []
    sum1 = []
    sum2 = []
    for i in soup.tbody:
        i = str(i)
        data = re.findall(finddataset2020, i)
        if (len(data) > 0):
            if (len(data) == 1):
                districtname.append(data[0])
            if (len(data) == 3):
                sum1.append(data[1])
                sum2.append(data[2])
    for i in range(0,2):
        district = []
        district.append(year)
        district.append(districtname[i])
        district.append(sum1[i])
        district.append(sum2[i])
        districtList.append(district)
    district = []
    district.append(year)
    district.append("盐田区")
    district.append(12)
    district.append(3003.25)
    districtList.append(district)
    for i in range(4,10):
        district = []
        district.append(year)
        district.append(districtname[i])
        district.append(sum1[i])
        district.append(sum2[i])
        districtList.append(district)
    return districtList

def get2019():
    url="http://swj.sz.gov.cn/xxgk/zfxxgkml/szswgk/tjsj/psgltj/content/post_8123700.html"
    html = ask(url)
    soup = BeautifulSoup(html, "html.parser")
    year = "2019"
    districtList = []

    districtname = []
    sum1 = []
    sum2 = []
    for i in soup.tbody:
        i = str(i)
        data = re.findall(finddataset2020, i)
        if (len(data) > 0):
            if (len(data) == 1):
                districtname.append(data[0])
            if (len(data) == 3):
                sum1.append(data[1])
                sum2.append(data[2])
    for i in range(0,7):
        district = []
        district.append(year)
        district.append(districtname[i])
        district.append(sum1[i])
        district.append(sum2[i])
        districtList.append(district)
    return districtList

def initdb(sql):
    db=pymysql.connect(host="localhost",user="root",password="root",port=3307,database="student",charset='utf8')
    try:
        cursor=db.cursor()
        data=cursor.execute(sql)
        print(data)
        result=cursor.fetchall()
        for i in result:
            print(i)
        db.commit()
    except Exception as e:
        db.rollback()
        print(str(e))
    finally:
        db.close()
def usedb():
    a=get2021()
    for item in a:
           item[1]='"'+item[1]+'"'
           sql = "insert into sewage (year,district,scale,sewagetreatment)values(%s,%s,%s,%s)"%(item[0],item[1],item[2],item[3])
           initdb(sql)
    b=get2020()
    for item in b:
        item[1] = '"' + item[1] + '"'
        sql = "insert into sewage (year,district,scale,sewagetreatment)values(%s,%s,%s,%s)" % (item[0], item[1], item[2], item[3])
        initdb(sql)
    c=get2019()
    for item in c:
        item[1] = '"' + item[1] + '"'
        sql = "insert into sewage (year,district,scale,sewagetreatment)values(%s,%s,%s,%s)" % (item[0], item[1], item[2], item[3])
        initdb(sql)
def getDatafromDB():
    sql="select * from sewage"
    db = pymysql.connect(host="localhost", user="root", password="root", port=3307, database="student", charset='utf8')
    try:
        cursor = db.cursor()
        data = cursor.execute(sql)
        result = cursor.fetchall()
        db.commit()
    except Exception as e:
        db.rollback()
        print(str(e))
    finally:
        db.close()
    return result
def getDatafrom(sql):
    db = pymysql.connect(host="localhost", user="root", password="root", port=3307, database="student", charset='utf8')
    try:
        cursor = db.cursor()
        data = cursor.execute(sql)
        result = cursor.fetchall()
        db.commit()
    except Exception as e:
        db.rollback()
        print(str(e))
    finally:
        db.close()
    return result











if __name__=="__main__":
    sql = "select sum(sewagetreatment) from sewage where year=2019"
    sql1 = "select sum(sewagetreatment) from sewage where year=2020"
    r=getDatafrom(sql)
    q=getDatafrom(sql1)
    print(int(r[0][0]))
    print(int(q[0][0]))